<?php

/**
 * SPU聚合
 *
 * @author    Y.N
 * @update	2011/5/13
 */
class CLI_Aggregate extends CLI
{

	/**
	 * CLI主方法
	 *
	 * @param	none
	 * @access	public
	 * @return	void
	 * @update	2011/5/13
	*/
	public function main()
	{
		$date = date('Ymd');
		$type = $this->args->type ? (string) $this->args->type : '4';
		$filename = TMPPATH.'/logs/cli/'.$date.'.sql';
		if ( ! file_exists($filename))
		{
			$content = "set names Utf8;\r\n";
			$content .= "use hqb;\r\n";
			@file_put_contents($filename, $content);
		}
		if ($type == '4')
		{
			self::cat_find_spu();
		}
		elseif ($type == '3')
		{
		    self::brand_find_spu();
		}
		elseif ($type == '2')
		{
			self::spu_find_spu();
		}
		CLI::output("\n新增spu完毕\n");
		exit;

	} // end func

	/**
	 * 根据类目抓取spu
	 *
	 * @param	none
	 * @access	public
	 * @return	void
	 * @update	2011/5/13
	*/
	public static function cat_find_spu()
	{
		// 获取类目
		$select = self::get_aggregate('1');
		$iterator = new Db_Iterator($select, 'default', 20);
		$date = date('Ymd');
		$update = strtotime(date('Y-m-d'));
		while ($iterator->valid())
		{
			foreach ($iterator->current() as $row)
			{
				CLI::output("\n正在获取类目".$row['sid']."的SPU.................\n");

				// 获取spu数
				$spu = DB::select('cid', 'product_id')
					->from('cats_products')
					->where('cid', '=', $row['sid']);
				$spu_iterator = new Db_Iterator($spu, 'default', 20);

				// 统计总数
				$total = $spu_iterator->total();
				CLI::output("\n类目".$row['sid']."下共 ".$total." 个SPU\n");

				self::catch_spu_data($spu_iterator, $row);
				unset($spu);

				// 更新已抓取时间为当天时间
				$filename = TMPPATH.'/logs/cli/'.$date.'.sql';
				$sql = DB::update('aggregate')
					->set(array('created_time' => $update))
					->where('id', '=', $row['id']);
				@file_put_contents($filename, $sql.";\r\n", FILE_APPEND);


				unset($spu_iterator, $row);
			}
			$iterator->next();
		}
		unset($iterator);
	} // end func

	/**
	 * 根据品牌抓取spu
	 *
	 * @param	none
	 * @access	public
	 * @return	void
	 * @update	2011/5/16
	*/
	public static function brand_find_spu()
	{
		$date = date('Ymd');
		$update = strtotime(date('Y-m-d'));
		$filename = TMPPATH.'/logs/cli/'.$date.'.sql';

		// 获取品牌
		$select = self::get_aggregate('2');
		$iterator = new Db_Iterator($select, 'default', 20);
		while ($iterator->valid())
		{
			foreach ($iterator->current() as $row)
			{
				CLI::output("\n正在获取品牌".$row['sid']."的SPU\n");

				// 获取spu数
				$spu = DB::select('cid', 'props', 'props_str', 'product_id', 'product_name', 'binds_str', 'image_md5')
					->from('products')
					->where('status', '=', 3)
					->where('props', 'REGEXP', '^'.$row['sid'].';');
				$spu_iterator = new Db_Iterator($spu, 'default', 20);
				unset($spu);

				// 统计总数
				$total = $spu_iterator->total();
				CLI::output("\n品牌".$row['sid']."下共 ".$total." 个SPU\n开始抓取数据");

				self::catch_spu_data($spu_iterator, $row);
				// 更新已抓取时间为当天时间
				$sql = DB::update('aggregate')
					->set(array('created_time' => $update))
					->where('id', '=', $row['id']);
				@file_put_contents($filename, $sql.";\r\n", FILE_APPEND);

				unset($spu_iterator, $row);
			}
			$iterator->next();
		}
		unset($iterator);
	} // end func

	/**
	 * 根据SPU抓取
	 *
	 * @param	none
	 * @access	public
	 * @return	void
	 * @update	date time
	*/
	public function spu_find_spu()
	{
		$date = date('Ymd');
		$filename = TMPPATH.'/logs/cli/'.$date.'.sql';

		// 获取类目
		$select = self::get_aggregate('3');
		$iterator = new Db_Iterator($select, 'default', 20);
		$time = time();
		while ($iterator->valid())
		{
			foreach ($iterator->current() as $row)
			{
				// 获取spu数
				$spu = DB::select('cid', 'props', 'props_str', 'product_id', 'product_name', 'binds_str', 'image_md5')
					->from('products')
					->where('status', '=', 3)
					->where('product_id', '=', $row['sid']);
				$spu_iterator = new Db_Iterator($spu, 'default', 20);
				unset($spu);

				// 统计总数
				CLI::output("\nSPU".$row['sid']."开始抓取数据\n");
				self::catch_spu_data($spu_iterator, $row);

				// 更新已抓取时间为当天时间
				$sql = DB::update('aggregate')
					->set(array('created_time' => $time))
					->where('id', '=', $row['id']);
				@file_put_contents($filename, $sql.";\r\n", FILE_APPEND);

				unset($spu_iterator, $row);
			}
			$iterator->next();
		}
		unset($iterator);
	} // end func

	/**
	 * 抓取spu的商品
	 *
	 * @param	array $spu
	 * @access	public
	 * @return	void
	 * @update	2011/5/14
	*/
	public static function get_spu_goods($spu, $row)
	{
		$return = array();

		// 查找规则表
		$least = DB::select('least')
			->from('aggregate')
			->where('sid', '=', $spu['product_id'])
			->where('type', '=', '3')
			->where('enable', '=', '1')
			->fetch_one();

		$leastday = DB::select('leastday')
			->from('aggregate')
			->where('sid', '=', $spu['cid'])
			->where('type', '=', '1')
			->where('enable', '=', '1')
			->fetch_one();

		// 如果存在，更改规则
		if ($least)
		{
			$row['least'] = $least;
		}

		CLI::output("\n开始抓取SPU：".$spu['product_id']." 下的商品\n");

		// 保存spu的商品ID
		$rows = DB::select('goods_id')
			->from('goods_products')
			->where('product_id', '=', $spu['product_id'])
			->fetch_all();
		if (empty($rows))
		{
			return $return;
		}

		foreach ($rows as $k=> $r)
		{
			$goods = DB::select('goods_id', 'shop_id', 'product_id', 'is_top', 'sales', 'retail_price', 'stock', 'updated', 'handle_id')
				->from('goods')
				->where('goods_id', '=', $r['goods_id'])
				->fetch_row();
			if ( ! empty($goods))
			{
				$shops = DB::select('protections', 'search_score', 'actives', 'super_protect')
					->from('shops')
					->where('shop_id', '=', $goods['shop_id'])
					->fetch_row();
			}
			$rows[$k] = array_merge($goods, $shops);
		}

		// 搜索规则排序
		usort($rows, 'CLI_Aggregate::my_sort_top');


		$sales = 0; $stock = 0; $rate_nums = 0; $rate_scores = 0; $scores_total = 0; $pic_url = array(); $ids = array(); $prices = array(); $shops = array();

		// 查找产品规则表
		$aggregate_product = DB::select('set_price', 'direct_price', 'min_price', 'max_price', 'percent', 'specific_price', 'set_bias', 'sort_by')
			->from('aggregate_product')
			->where('product_id', '=', $spu['product_id'])
			->where('status', '=', '1')
			->fetch_row();

		foreach ($rows as $k => $goods)
		{
			// 取消国货
			if ($goods['handle_id'] == 5)
			{
				continue;
			}

			// 相差天数
			$differ_day=round((time()-$goods['updated'])/3600/24);

			if ($differ_day>$leastday)
			{
				continue;
			}

			// 是否启用价格设置
			if ($aggregate_product['set_price'])
			{
				// 是否使用价格或偏差
				if ($aggregate_product['sort_by']=='0')
				{
					if ($goods['retail_price']<$aggregate_product['min_price'])
					{
						continue;
					}
					if ($goods['retail_price']>$aggregate_product['max_price'])
					{
						continue;
					}
				} else {
				    if ($goods['retail_price']/$aggregate_product['direct_price']*100>$aggregate_product['percent'] AND ! $aggregate_product['set_bias'])
					{
						continue;
					}
					if (abs($goods['retail_price']-$aggregate_product['direct_price'])>$aggregate_product['specific_price'] AND $aggregate_product['set_bias'])
					{
						continue;
					}
				}
			}
			if (count($pic_url)<5)
			{
				// 取出产品图片
				$product_images = DB::select('image_md5')
					->from('product_imgs')
					->where('product_id', '=', $spu['product_id'])
					->fetch_all();

				foreach ($product_images as $p)
				{
					// 过滤重复图片
					if ( ! in_array($p['image_md5'], $pic_url))
					{
						$pic_url[] = $p['image_md5'];
					}
				}
				unset($product_images);
			}
			if (count($pic_url)<5)
			{
				// 取出商品图片
				$goods_images = DB::select('image_md5')
					->from('goods_images')
					->where('goods_id', '=', $goods['goods_id'])
					->fetch_all();
				foreach ($goods_images as $g)
				{
					// 过滤重复图片
					if ( ! in_array($g['image_md5'], $pic_url))
					{
						$pic_url[] = $g['image_md5'];
					}
				}
				unset($goods_images);
			}

			// 获取商品评分
			$rate_stat = Detail::get_rate_stat($goods['shop_id'], $goods['goods_id']);
			$rate_scores_new = round($rate_stat['score'],1) ;
			if ($rate_scores_new)
			{
				$scores_total++;
			}
			$rate_nums_new = $rate_stat['num'];

			// 需要返回的数据
			$rate_scores = $rate_scores+$rate_scores_new;
			$rate_nums = $rate_nums+$rate_nums_new;
			$sales = $sales + $goods['sales'];
			$stock = $stock + $goods['stock'];
			$ids[] = $goods['goods_id'];
			$prices[] = $goods['retail_price'];
			$shops[] = $goods['shop_id'];
		}
		if (empty($ids))
		{
			return $return;
		}
		$shops = array_unique($shops);
		$shop_num = count($shops);
		if ($shop_num< $row['least'])
		{
			return $return;
		}
		$return['overall'] = $scores_total != 0 ? round($rate_scores/$scores_total,1) : 0;
		$return['people'] = $rate_nums;
		$return['pic_url'] = implode('|', $pic_url);
		$return['ids'] = implode(',', $ids);
		$return['sales'] = $sales;
		$return['stock'] = $stock;
		$return['business'] = $shop_num;
		$return['price'] = array_sum($prices)/count($prices);
		unset($iterator);
		return $return;
	}

	/**
	 * 写入数据
	 *
	 * @param	string $spu_iterator
	 * @access	public
	 * @return	void
	 * @update	2011/5/17
	*/
	public static function catch_spu_data($spu_iterator, $row)
	{
		$date = date('Ymd');
		$filename = TMPPATH.'/logs/cli/'.$date.'.sql';

	    while ($spu_iterator->valid())
		{
			CLI::output("...");
			foreach ($spu_iterator->current() as $p)
			{
				$is_exist = DB::select()
					->from('nav_cats_map')
					->where('cid', '=', $p['cid'])
					->fetch_one();
				if ( ! $is_exist)
				{
					continue;
				}

				$return = self::get_spu_goods($p, $row);

				// 如果返回的数据不为空
				if ($return)
				{
					$products = DB::select('props', 'props_str', 'binds_str', 'cid', 'product_id')
						->from('products')
						->where('product_id', '=', $p['product_id'])
						->fetch_row();

					if (empty($products['props_str']) OR ! stristr($products['props_str'], ';'))
					{
						continue;
					}

					//  取出品牌属性ID串
					$props_array = explode(';', $products['props']);
					$brand_id = current($props_array);

					// 取出第一张图片
					$images = explode('|', $return['pic_url']);
					$image_md5 = current($images);

					// spu属性中文字串
					$props_str = explode(';', $products['props_str']);

					$brand_new = array();
					$model_array = array();

					// 获取品牌名称字串, 并进行分割
					$brand_str = current($props_str);

					// 品牌名
					$brand_name = explode(':', $brand_str);
					$brand_retail_name = end($brand_name);
					if (stristr($brand_retail_name, '/'))
					{
						$brand_zw_name = str_replace('/', '', stristr($brand_retail_name, '/'));
					} else {
					    $brand_zw_name = $brand_retail_name;
					}

					$props_str = explode(":", end($props_str));
					$model_name = end($props_str);

					// 需要新增的字段
					$replace = array(
						'product_id' => $products['product_id'],
						'product_name' => $brand_zw_name. ' '.trim($model_name),
						'brand_id' => $brand_id,
						'props' => $products['props'],
						'props_str' => $products['props_str'].';'.$products['binds_str'],
						'cid' => $products['cid'],
						'pic_url' => $return['pic_url'],
						'image_md5' => $image_md5,
						'status' => (int) $row['enable'],
						'ids' => $return['ids'],
						'price' => $return['price'],
						'business' => $return['business'],
						'overall' => $return['overall'],
						'people' => $return['people'],
						'sales' => $return['sales'],
					);

					// 是否已经存在
					$ok = DB::select('modify', 'product_id')
						->from('aggregate_product')
						->where('product_id', '=', $p['product_id'])
						->fetch_row();

					// 如果后台没有操作过，则以下字段进行更新
					if ($ok['modify'] != 2)
					{
						$replace['stock'] = $return['stock'];
						$replace['modify'] = 3;
					}

					// 如果数据不存在
					if (empty($ok))
					{
						//  插入数据
						$sql = DB::insert('aggregate_product', array_keys($replace))
							->values(array_values($replace));
						@file_put_contents($filename, $sql.";\r\n", FILE_APPEND);

					}else {
						// 更新
					    $sql = DB::update('aggregate_product')
							->set($replace)
							->where('product_id', '=', $ok['product_id']);
							@file_put_contents($filename, $sql.";\r\n", FILE_APPEND);
					}

					// 获取此spu所在八大类目的url
					$cat_path = DB::select('cat_path')
						->from('nav_cats_map')
						->where('cid', '=', $p['cid'])
						->fetch_one();
					$top_cid = explode(';', $cat_path);
					$url = DB::select('url')
						->from('nav_cats_map')
						->where('catid', '=', current($top_cid))
						->fetch_one();
					if (empty($url))
					{
						$url = "digi.360hqb.com";
					}

					// spu属性中文字串

					if (stristr($brand_retail_name, '/'))
					{
						$brand_new = explode('/', $brand_retail_name);
					}else {
						$brand_new[] = $brand_retail_name;
					}

					// 获取型号名

					$model_array[] = $model_name;
					if (stristr($model_name, '-'))
					{
						$sub_name = explode('-', $model_name);
						$model_array[] = next($sub_name);
					}

					// 录入数据
					foreach ($brand_new as $b)
					{
						foreach ($model_array as $m)
						{
							if ( ! empty($m))
							{
								$m = preg_replace("/".strtoupper($b)."\s?/", '', strtoupper($m));
								$name = strtoupper($b.' '.$m);
								self::replace_prop($name, $p['product_id'], $row['enable'], $filename, $url, 3);
							}
						}
					}

					// 录入数据
					foreach ($model_array as $m)
					{
						self::replace_prop($m, $p['product_id'], $row['enable'], $filename, $url, 4);
					}

					unset($brand_new, $model_array);

					CLI::output("\n新增spu：".$p['product_id']."\n");
				}else {

					// 更新
					$sql = DB::update('aggregate_product')
						->set(array('status' => 2, 'created_time' => time()))
						->where('product_id', '=', $p['product_id']);
					$sql_r = $sql;
					$ok = $sql->execute();

					if ($ok)
					{
						@file_put_contents($filename, $sql_r.";\r\n", FILE_APPEND);
						// 删除关键字匹配表状态
						$sql = DB::delete('aggregate_prop')
							->where('sid', '=', $p['product_id'])
							->where('type', '=', '3')
							->where('del', '<>', 1);
						@file_put_contents($filename, $sql.";\r\n", FILE_APPEND);
					}
					CLI::output(".");
				}
			}
			$spu_iterator->next();
		}
		unset($spu_iterator);
	}

	/**
	 * 规则表数据获取
	 *
	 * @param	none
	 * @access	public
	 * @return	void
	 * @update	2011/5/13
	*/
	public static function get_aggregate($type = NULL)
	{
		return DB::select('*')
			->from('aggregate')
			->where('type', '=', $type)
			->where('created_time', '<', time());
	}

	// 定义排序函数 总和排序
	public static function my_sort_top($a, $b)
	{
		$a = $a['is_top']+$a['super_protect']+strlen($a['protections'])+$a['actives']+$a['search_score'];
		$b = $b['is_top']+$b['super_protect']+strlen($b['protections'])+$b['actives']+$b['search_score'];
		 if ($a == $b) return 0;
		 return ($a > $b) ? -1 : 1;
	}

	// 定义排序函数 HQB保
	public static function my_sort_super_protect($a, $b)
	{
		 if ($a['super_protect'] == $b['super_protect']) return 0;
		 return ($a['super_protect'] > $b['super_protect']) ? -1 : 1;
	}

	// 定义排序函数 消保
	public static function my_sort_protect($a, $b)
	{
		 if ($a['protections'] == $b['protections']) return 0;
		 return (strlen($a['protections']) > strlen($b['protections'])) ? -1 : 1;
	}

	// 定义排序函数 活跃度
	public static function my_sort_actives($a, $b)
	{
		 if ($a['actives'] == $b['actives']) return 0;
		 return ($a['actives'] > $b['actives']) ? -1 : 1;
	}

	// 定义排序函数 搜索权重
	public static function my_sort_search($a, $b)
	{
		 if ($a['search_score'] == $b['search_score']) return 0;
		 return ($a['search_score'] > $b['search_score']) ? -1 : 1;
	}


	/**
	 * 关键字匹配度操作
	 *
	 * @param	none
	 * @return	void
	 * @update	2011/7/18
	 */
	public static function replace_prop($name, $sid, $enable, $filename, $url, $type)
	{
		if ($enable == '2')
		{
			// 删除关键字匹配表状态
			$sql = DB::delete('aggregate_prop')
				->where('sid', '=', $p['product_id'])
				->where('type', '=', $type)
				->where('del', '=', 0);
			@file_put_contents($filename, $sql.";\r\n", FILE_APPEND);
			continue;
		}

		// 是否存在数据
		$aggregate_prop = DB::select('del', 'id')
			->from('aggregate_prop')
			->where('type', '=', $type)
			->where('sid', '=', $sid)
			->where('name', '=', trim($name))
			->fetch_row();

		// del:2为未启用，但不是已删除
		if ( ! empty($aggregate_prop) AND $aggregate_prop['del'] > 1)
		{
			// 更改为启用状态
			$set = array(
				'del' => 0,
				'name' => $name,
			);
			$sql = DB::update('aggregate_prop')
				->set($set)
				->where('id', '=', $aggregate_prop['id']);
			@file_put_contents($filename, $sql.";\r\n", FILE_APPEND);
		}
		elseif (empty($aggregate_prop))
		{
			$insert = array(
				'type' => $type,
				'del' => 0,
				'sid' => $sid,
				'name' => $name,
				'url' => $url,
			);
			$sql = DB::insert('aggregate_prop', array_keys($insert))
				->values(array_values($insert));
			@file_put_contents($filename, $sql.";\r\n", FILE_APPEND);
		}
	}
}


